package org.hcf.utils.excel;

import com.google.gson.JsonObject;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;


import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelToJsonList {
    /** 模板文件的绝对路径 */
    private String tplFilePath;
    private File tplFile;


    /**每一列值所对应的key值:key=json的key所在的列，val=jaon的key*/
    private Map jsonKeys = new LinkedHashMap();
    /** jsonKeys所在的行（从0开始的），默认是第二行 *///  由于该功能仅支持简单的模板，需要指定，tplJsonKeys所在的行,
    private int jsonKeyRowNum = 1;
    /** jsonKeys开始的列号（从0开始的）*/
    private int jsonKeyRowColStart = 0;

    /** key的中文注释：key=json的key所在的列，val=json的key的desc */
    private Map jsonKeysDesc = new LinkedHashMap();
    /** key的中文注释所在的行（从0开始的），默认为第一行*/
    private int jsonKeysDescRowNum = 0;



    /** 数据文件的绝对路径 */
    private String dataFilePath;
    private File dataFile;
    /** 数据开始的行号（从0开始的），默认是第二行,数据开始的列号应该与key开始的列号是一致的 */
    private int dataRowNumStart = 1;
    /** 最多读多少行*/
    private int maxDataRowNums = Integer.MAX_VALUE;

    /** 最后将dataFile转换为的数据*/
    private List jsons = new ArrayList();
    /**  长度不大于16位（不以数字开头的数字，字母下划线组成的key）*/
    private String jsonKeyReg = "^\\$\\$[a-z|A-Z|_][a-z|A-Z|_|0-9]{0,15}\\$\\$$";


    private ExcelToJsonList(){
        super();
    }
    public ExcelToJsonList(String tplFilePath,String dataFilePath){
        this();
        this.setTplFilePath(tplFilePath);
        this.setDataFilePath(dataFilePath);
        init();
    }

    public void init(){
        this.setTplFile(new File(tplFilePath));
        this.setDataFile(new File(dataFilePath));
        if(!this.getTplFile().exists() || !this.getDataFile().exists()){
            throw new RuntimeException("模板文件或数据文件不存在！请校验");
        }
    }
    /** 初始化json对象的每一列的key */
    public boolean initJsonKeys(){
        boolean retFlag = false;
//        读取模板文件
        Workbook workbook= null;
        try {
            workbook=Workbook.getWorkbook(this.getTplFile());
            Sheet sheet=workbook.getSheet(0);//获取第一个工作簿
            int rows = sheet.getRows();
            int cols = sheet.getColumns();
//            $$jsonKey$$
            String keyReg = this.jsonKeyReg;//长度不大于16位（不以数字开头的数字，字母下划线组成的key）
            if(rows<this.getJsonKeyRowNum()){
                new RuntimeException("给定或默认的jsonKeys所在行号与模板文件不符合");
            }
//           行是固定的行，列
            for(int col=jsonKeyRowColStart;col<cols;col++){
                // 遍历列
                Cell cell=sheet.getCell(col,this.getJsonKeyRowNum());
                String cellContent = cell.getContents();
                if(cellContent.matches(keyReg)){//符合key的正则表达式
//                    key=列号 ，val =去掉$符号的值
                    this.jsonKeys.put(String.valueOf(col),String.valueOf(cellContent.replaceAll("\\$","")));
                    Cell descCell = sheet.getCell(col,this.getJsonKeysDescRowNum());
                    this.getJsonKeysDesc().put(String.valueOf(cellContent.replaceAll("\\$","")),descCell.getContents());
                    retFlag = true;
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }finally{
            if(workbook != null){workbook.close();}
        }
        return retFlag;
    }

    public List excel2JsonList(){
        // 初始化json的列名
        boolean initkey = initJsonKeys();
        if(!initkey){
            throw new RuntimeException("初始化json的key出现异常");
        }
        List jsonList = this.getJsons();
        jsonList.clear();//清空
//        读取模板文件
        Workbook workbook= null;
        try {
            workbook=Workbook.getWorkbook(this.getDataFile());
            Sheet sheet=workbook.getSheet(0);//获取第一个工作簿
            int rows = sheet.getRows();
            int cols = sheet.getColumns();
            for( int row = dataRowNumStart; row<rows && (row-dataRowNumStart+1)<maxDataRowNums; row++ ){
                JsonObject jsonObject = new JsonObject();
                for(int col= jsonKeyRowColStart; col<cols; col++ ){//数据开始的列于key开始的列是一至的
                    Cell cell = sheet.getCell(col,row);
                    String colnum = String.valueOf(cell.getColumn());
                    String jsonKey = (String) this.jsonKeys.get(colnum);//根据列获取key
                    if(jsonKey!=null&& !"".equals(jsonKey.trim())){
                        String jsonVal = cell.getContents();
                        if(jsonVal==null || "".equals(jsonVal.trim())){
                            jsonVal = "";
                        }else{
                            jsonVal = jsonVal.trim();
                        }
                        //初始化json的数据
                        jsonObject.addProperty(String.valueOf(jsonKey),jsonVal);
                    }
                }
                jsonObject.addProperty("row",String.valueOf(row+this.getDataRowNumStart()));
                jsonList.add(jsonObject);
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } finally{
            if(workbook != null){workbook.close();}
        }
        return this.getJsons();
    }

    /** 通过反射技术将excel转换为obj对象(反射效率会比较慢)
     * 目前支持：String、int（Integer），double(Double),BigDecimal
     */
    public List Excel2Obj( Class classType ){
        List objs = new ArrayList();
        boolean initkey = initJsonKeys();
        if(!initkey){
            throw  new RuntimeException("初始化字段失败");
        }
        Workbook workbook= null;
        try {
            Class cls= classType;
            workbook=Workbook.getWorkbook(this.getDataFile());
            Sheet sheet=workbook.getSheet(0);//获取第一个工作簿
            int rows = sheet.getRows();
            int cols = sheet.getColumns();
            for( int row = dataRowNumStart; row<rows && (row-dataRowNumStart+1)<maxDataRowNums; row++ ){
                Object obj = cls.newInstance();//反射创建对象
                for(int col= jsonKeyRowColStart; col<cols; col++ ){//数据开始的列于key开始的列是一至的
                    Cell cell = sheet.getCell(col,row);
                    String colnum = String.valueOf(cell.getColumn());
                    String jsonKey = (String) this.jsonKeys.get(colnum);//根据列获取key
                    if(jsonKey!=null&& !"".equals(jsonKey.trim())){
                        String jsonVal = cell.getContents();
                        if(jsonVal==null || "".equals(jsonVal.trim())){
                            jsonVal = "";
                        }else{
                            jsonVal = jsonVal.trim();
                        }
                        //反射字段赋值
                        String fieldName = (String) this.getJsonKeys().get(colnum);
                        Field field = cls.getDeclaredField(fieldName);
                        field.setAccessible(true);
                        String setName = "set"+fieldName.substring(0,1).toUpperCase().concat(fieldName.substring(1));
                        Method setMethod = cls.getMethod(setName,field.getType());
                        /*** 拓展类型 ****/
                        if(field.getType().equals(String.class)){
                            setMethod.invoke(obj,jsonVal);
                        }else if(field.getType().equals(int.class) ){
                            setMethod.invoke(obj,Integer.parseInt(jsonVal) );
                        }else if(field.getType().equals(Integer.class)){
                            setMethod.invoke(obj,Integer.valueOf(jsonVal) );
                        }else if(field.getType().equals(double.class)){
                            setMethod.invoke(obj,Double.parseDouble(jsonVal) );
                        }else if(field.getType().equals(Double.class)){
                            setMethod.invoke(obj,Double.valueOf(jsonVal));
                        }else if(field.getType().equals(BigDecimal.class)){
                            setMethod.invoke(obj,BigDecimal.valueOf(Double.valueOf(jsonVal)));
                        }
                    }
                }
                objs.add(obj);
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally{
            if(workbook != null){workbook.close();}
        }

        return objs;
    }

    /* ========================================================================= */
    /*                               getter and setter                           */
    /* ========================================================================= */
    public String getTplFilePath() {
        return tplFilePath;
    }

    public void setTplFilePath(String tplFilePath) {
        this.tplFilePath = tplFilePath;
    }

    public File getTplFile() {
        return tplFile;
    }

    public void setTplFile(File tplFile) {
        this.tplFile = tplFile;
    }

    public Map getJsonKeys() {
        return jsonKeys;
    }

    public void setJsonKeys(Map jsonKeys) {
        if(jsonKeys==null)
            return;
        this.jsonKeys = jsonKeys;
    }

    public int getJsonKeyRowNum() {
        return jsonKeyRowNum;
    }

    public void setJsonKeyRowNum(int jsonKeyRowNum) {
        this.jsonKeyRowNum = jsonKeyRowNum;
    }

    public int getJsonKeyRowColStart() {
        return jsonKeyRowColStart;
    }

    public void setJsonKeyRowColStart(int jsonKeyRowColStart) {
        this.jsonKeyRowColStart = jsonKeyRowColStart;
    }

    public String getJsonKeyReg() {
        return jsonKeyReg;
    }

    public void setJsonKeyReg(String jsonKeyReg) {
        this.jsonKeyReg = jsonKeyReg;
    }

    public Map getJsonKeysDesc() {
        return jsonKeysDesc;
    }

    public void setJsonKeysDesc(Map jsonKeysDesc) {
        this.jsonKeysDesc = jsonKeysDesc;
    }

    public int getJsonKeysDescRowNum() {
        return jsonKeysDescRowNum;
    }

    public void setJsonKeysDescRowNum(int jsonKeysDescRowNum) {
        this.jsonKeysDescRowNum = jsonKeysDescRowNum;
    }

    public String getDataFilePath() {
        return dataFilePath;
    }

    public void setDataFilePath(String dataFilePath) {
        this.dataFilePath = dataFilePath;
    }

    public File getDataFile() {
        return dataFile;
    }

    public void setDataFile(File dataFile) {
        this.dataFile = dataFile;
    }

    public int getDataRowNumStart() {
        return dataRowNumStart;
    }

    public void setDataRowNumStart(int dataRowNumStart) {
        this.dataRowNumStart = dataRowNumStart;
    }

    public int getMaxDataRowNums() {
        return maxDataRowNums;
    }

    public void setMaxDataRowNums(int maxDataRowNums) {
        this.maxDataRowNums = maxDataRowNums;
    }

    public List getJsons() {
        return jsons;
    }

    public void setJsons(List jsons) {
        this.jsons = jsons;
    }
}
